package mo.org.cm309.shoppingcart.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import mo.org.cm309.shoppingcart.entity.User;

import org.apache.log4j.Logger;

public class UserDAO extends BaseDAO {
	
	private static UserDAO instance;
	static Logger log = Logger.getLogger(UserDAO.class);
	public UserDAO() {
		super();
	}
	
	public static synchronized UserDAO getInstance() {
		log.info("Get Connection.");
		if (instance == null) {
			instance = new UserDAO();
		}
		return instance;
	}
	
	//INSERT A USER
	//INPUT : USER (OBJECT)
	//OUTPUT: SUCCESS: USERID (LONG)
	//        FAIL   : -1     (LONG)
	public long insert(User user) {
		Connection conn = null;
		Statement stmt = null;

		try {
			conn = getConnection();
			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			String sql;
			
			if (user.getCardExpiry()!=null) 
				sql = "insert into user (firstname, lastname, email, password, phone_num, address, credit_card, card_expiry, role_id) values("
						+ "'" + user.getFirstname() + "', '" + user.getLastname() + "', '" + user.getEmail() + "', '" + user.getPassword() + "', '" + user.getPhoneNum() + "', '" + user.getAddress() + "', '" + user.getCreditCard() + "', '" + user.getCardExpiry() + "', " + user.getRoleId() + ")";
			else
				sql = "insert into user (firstname, lastname, email, password, phone_num, address, credit_card, role_id) values("
						+ "'" + user.getFirstname() + "', '" + user.getLastname() + "', '" + user.getEmail() + "', '" + user.getPassword() + "', '" + user.getPhoneNum() + "', '" + user.getAddress() + "', '" + user.getCreditCard() + "', " + user.getRoleId() + ")";
			
			log.info(sql);
			stmt.execute(sql);
			ResultSet lastId = conn.createStatement().executeQuery(
					"select last_insert_id() as ID from user limit 1");
			if (lastId.next()) {
				user.setId(lastId.getLong("ID"));
			}
			conn.commit();
			return user.getId();

		} catch (SQLException ex) {
			if (conn != null){
				try {
					conn.rollback();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			ex.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				log.error(e);
			}
			releaseConnection(conn);
		}
		return -1;
	}
	
	//UPDATE A USER
	//INPUT : USER (OBJECT)
	public void update(User user) {
		Connection conn = null;
		Statement stmt = null;
		DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		String sql = "";
		try {
			conn = getConnection();
			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			if (user.getCardExpiry()!=null) 
				sql = "update user set "  +
					  "firstname = '"     +  user.getFirstname() + "', " +
					  "lastname  = '"     +  user.getLastname()  + "', " +
					  "email     = '"     +  user.getEmail()     + "', " +
					  "password  = '"     +  user.getPassword()  + "', " +
					  "phone_num = '"     +  user.getPhoneNum()  + "', " +
					  "address   = '"     +  user.getAddress()   + "', " +
					  "credit_card = '"   +  user.getCreditCard()+ "', " +
					  "card_expiry = '"   +  user.getCardExpiry()+ "', " +
					  "role_id     = "    +  user.getRoleId() 	 + " "  +
					  "where id    = "    +  user.getId()        + ";";	 
 			else
 				sql = "update user set "  +
				  "firstname = '"     +  user.getFirstname() + "', " +
				  "lastname  = '"     +  user.getLastname()  + "', " +
				  "email     = '"     +  user.getEmail()     + "', " +
				  "password  = '"     +  user.getPassword()  + "', " +
				  "phone_num = '"     +  user.getPhoneNum()  + "', " +
				  "address   = '"     +  user.getAddress()   + "', " +
				  "credit_card = '"   +  user.getCreditCard()+ "', " +
				  "role_id     = "    +  user.getRoleId() 	 + " "  +
				  "where id    = "    +  user.getId()        + ";";	 
			log.info(sql);
			stmt.execute(sql);
			conn.commit();
			log.info("Update User Profile Successful.");
		} catch (SQLException ex) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error(e);
			}
			log.error(ex);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				log.error(e);
			}
			releaseConnection(conn);
		}

	}
	
	//DELERE A USER
	//INPUT : USER (OBJECT, JUST ONLY INCLUDE USERID)
	public void delete(User user) {
		Connection conn = null;
		Statement stmt = null;

		try {
			conn = getConnection();
			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			String sql = "delete from user " + 
					"where id= '" + user.getId() + "'";
			log.info(sql);
			stmt.execute(sql);
			conn.commit();
			log.info("Deleted User : " + user.getId());
		} catch (SQLException ex) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				log.error(e);
			}
			log.error(ex);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				log.error(e);
			}
			releaseConnection(conn);
		}
	}
	
	//FIND SOME USERS
	//INPUT : WHERECLAUSE (STRING), ORDERCLAUSE (STRING)
	//OUTPUT: LIST OF USER (OBJECT)
	public List<User> findByWhereOrderClause(String whereClause, String orderClause) {
		List<User> list = new ArrayList<User>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(true);
			stmt = conn.createStatement();
			String sql = "select * from user " + whereClause + " " + orderClause;
			log.info(sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				User u = new User();
				u.setId(rs.getLong("id"));
				u.setFirstname(rs.getString("firstname"));
				u.setLastname(rs.getString("lastname"));
				u.setEmail(rs.getString("email"));
				u.setPassword(rs.getString("password"));
				u.setPhoneNum(rs.getString("phone_num"));
				u.setAddress(rs.getString("address"));
				u.setCreditCard(rs.getString("credit_card"));
				u.setCardExpiry(rs.getString("card_expiry"));
				u.setRoleId(rs.getInt("role_id"));
				list.add(u);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			releaseConnection(conn);
		}

		return list;
	}
	
	
	//FIND SOME USERS
	//INPUT : WHERECLAUSE (STRING)
	//OUTPUT: LIST OF USER (OBJECT)
	public List<User> findByWhereClause(String whereClause) {
		return findByWhereOrderClause(whereClause, "");
	}
	
	//FIND ALL USERS THAT EXIST IN DATABASE
	//INPUT : NOTHING
	//OUTPUT: LIST OF USER (OBJECT)
	public List<User> findAll() {
		return findByWhereClause("");
	}
	
	//FIND A USER
	//INPUT : USER ID (LONG)
	//OUTPUT: USER (OBJECT)
	public User findById(long id) {
		List<User> us = findByWhereOrderClause("where id=" + id, "order by id");
		if (us.size() > 0)
			return (User) us.get(0);
		return null;
	}
	
	//FIND A USER
	//INPUT : EMAIL (STRING)
	//OUTPUT: USER (OBJECT)
	public User findByEmail(String email) {
		List<User> us = findByWhereOrderClause("where email='" + email + "'", "order by id");
		if (us.size() > 0)
			return (User) us.get(0);
		return null;
	}
	
}
